<?php

namespace app\admin\controller\work;

use app\admin\model\Appoint;
use app\api\controller\Sms;
use app\common\controller\Backend;
use app\common\model\cost\Cost;
use app\common\model\Equipment;
use app\common\model\Hospital;
use app\common\model\Workflow;
use think\Db;
use think\Exception;


use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
/**
 * 工单管理
 *
 * @icon fa fa-circle-o
 */
class Warranty extends Backend
{

    /**
     * Warranty模型对象
     * @var \app\common\model\work\Warranty
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\common\model\work\Warranty;
        $this->view->assign("statusList", $this->model->getStatusList());
        $this->view->assign("billingDataList", $this->model->getBillingDataList());
        $this->view->assign("typeDataList", $this->model->getTypeDataList());
        $this->view->assign("collectDataList", $this->model->getCollectDataList());
        $this->view->assign("commDataList", $this->model->getCommDataList());
        $this->view->assign("openDataList", $this->model->getOpenDataList());
    }



    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */


    /**
     * 查看
     */
    public function index()
    {
        //当前是否为关联查询
        $this->relationSearch = true;
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            $filter = $this->request->get("filter", '');
            $filter = (array)json_decode($filter, true);
            if (array_key_exists("status", $filter)) {
                if (in_array($filter["status"], [4, 5, 6, 7])) {
                    $op = json_decode($this->request->request("op"), true);
                    $filter["warranty_time"] = time();
                    $op["warranty_time"] = ">";
                    $filter["eng_com"] = 0;
                    $op["eng_com"] = "=";
                    $filter["is_loss"] = 0;
                    $op["is_loss"] = "=";
                    if (in_array($filter["status"], [5, 6])) $op["warranty_time"] = "<";
                    if ($filter["status"] == 5) {
                        $filter["ok_status"] = 0;
                        $op["ok_status"] = "=";
                    }
                    if ($filter["status"] == 6) {
                        unset($filter["eng_com"]);
                        unset($op["eng_com"]);
                        $filter["ok_status"] = 0;
                        $op["ok_status"] = "=";
                        $filter["eng_com"] = 1;
                        $op["eng_com"] = "=";
                    }
                    if ($filter["status"] == 3) {
                        $filter["ok_status"] = 1;
                        $op["ok_status"] = "=";
                    }
                    if ($filter["status"] != 7) {
                        $filter["status"] = 3;
                        $filter["is_loss"] = 0;
                        $op["is_loss"] = "=";
                    }

                    if ($filter["status"] == 7) {
                        unset($filter["eng_com"]);
                        unset($op["eng_com"]);
                        unset($filter["warranty_time"]);
                        unset($op["warranty_time"]);
                        unset($filter["status"]);
                        unset($op["status"]);
                        $filter["is_loss"] = 1;
                        $op["is_loss"] = "=";
                    }
                } else {
                    $filter["ok_status"] = 1;
                    $filter["is_loss"] = 0;
                    $op["is_loss"] = "=";
                    $op["ok_status"] = "=";
                    if ($filter["status"] != 7 && $filter["status"] != 3) {
                        $filter["ok_status"] = 0;
                    }
                }
                //dump($filter);die();
                $this->request->get(["filter" => json_encode($filter), 'op' => json_encode($op)]);
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $user = $this->auth->getUserInfo();
            $hospital = Hospital::field("id")->where(["status" => 1])
                ->where('FIND_IN_SET("' . $user["id"] . '", admin_ids)')->find();
            $where1 = '1=1';
            if ($hospital) $where1 = " warranty.hospital_id = " . $hospital["id"];
            $list = $this->model
                ->with(['user', 'hospital', 'equipment',
                    'engineer' => function ($query) {
                        $query->withField('id,nickname');
                    },
                    'sale' => function ($query) {
                        $query->withField('id,nickname');
                    }])
                ->where($where)
                ->where($where1)
                ->where(["warranty.type_data" => '0'])
                ->order($sort, $order)
                ->paginate($limit);
            $id=$this->model
                ->field("warranty.id ids")
                ->alias("warranty")
                ->join("user user","warranty.user_id=user.id","left")
                ->join("hospital hospital","warranty.hospital_id=hospital.id","left")
                ->join("equipment equipment","warranty.equipment_id=equipment.id","left")
                ->join("user engineer","warranty.engineer_id=engineer.id","left")
                ->join("user sale","warranty.sale_id=sale.id","left")
                ->where($where)
                ->where($where1)
                ->where(["warranty.type_data" => '0'])
                ->order($sort, $order)->select();
            $ids=[];
            foreach ($id as $k=>$v){
                $ids[]=$v["ids"];
            }
            /*dump($ids);die();
            $ids=$ids["ids"];*/
            $final_price_sum = 0;//成交价
            $receivable_sum = 0;//应收
            $profit_sum = 0;//利润
            foreach ($list as $k => $row) {
                $row->visible(['id', 'workID', 'ok_status', 'xshk', 'warranty_time', 'cost_money', 'receivable', 'open_data', 'comm_data', 'product', 'profit', 'device', 'contacts', 'mobile', 'status', 'billing_data', 'createtime', 'final_price', 'engineer_id', 'collect_data', 'collect_time', 'equipment_id'
                    , 'sale_id' , 'is_loss', 'user_id']);

                /*2022-11-22 李娜 新增 start*/
                $row->visible(['engineer']);
                $row->getRelation('engineer')->visible(['id', 'nickname']);
                $row->visible(['sale']);
                $row->getRelation('sale')->visible(['id', 'nickname']);
                /*2022-11-22 李娜 新增 end*/
                $row->visible(['user']);
                $row->getRelation('user')->visible(['username', 'nickname', 'avatar']);
                $row->visible(['equipment']);
                $row->getRelation('equipment')->visible(['id', 'name', 'marking', 'serial', 'enclosure', 'software_version']);
                $row->visible(['hospital']);
                $row->getRelation('hospital')->visible(['name']);
                $xshk = \app\admin\model\work\Xshk::where(["warranty_id" => $row["id"]])->sum("money");
                //$row->cost_money=Cost::where(["warranty_id"=>$row["id"]])->sum("money");
                $row->cost_money = Cost::where(["warranty_id" => $row["id"], "status" => '1'])->sum("money");
                $row->xshk = $xshk;
                $receivable = 0;
                /*if (!empty($row["final_price"]) && $row["final_price"] > 0) {
                    //$cost=Cost::where(["warranty_id"=>$row["id"],"status"=>'1'])->sum("money");
                    $row->profit = $row["final_price"] - $row->cost_money;
                    $receivable = $row["final_price"] - $xshk;
                    //$profit_sum+=$row->profit;
                } else {
                    $row->profit = "——";
                }*/
                $row->profit = $row["final_price"] - $row->cost_money;
                $receivable = $row["final_price"] - $xshk;
                $row->receivable = $receivable;
                /*新增*/
                if ($row["status"] == 3 && $row["warranty_time"] < time()) {
                    $row["is_loss"] = 1;
                }
                /*新增 end*/
                /*$final_price_sum+=$row["final_price"];
                $receivable_sum+=$receivable;*/
            }
            $final_price_sum = $this->model->where(["type_data" => '0'])->whereIn("id", $ids)->where($where1)->sum("final_price");//成交价
            $xshk = \app\admin\model\work\Xshk::with("warranty")->whereIn("warranty_id", $ids)->where($where1)->where(["type_data" => "0"])->sum("money");//回款记录
            $cost = Cost::with("warranty")->where(["type_data" => "0"])->whereIn("warranty_id", $ids)->where($where1)->where(["cost.status" => '1'])->sum("money");//成本
            $receivable_sum = $final_price_sum - $xshk;//应收账款  成交价-陈本
            $profit_sum = $final_price_sum - $cost;//利润  成交价-回款记录
            $result = array("total" => $list->total(), "rows" => $list->items(),
                "final_price_sum" => floor($final_price_sum * 100) / 100,
                "receivable_sum" => floor($receivable_sum * 100) / 100,
                "profit_sum" => floor($profit_sum * 100) / 100,
            );
            /*$result = array("total" => $list->total(), "rows" => $list->items(),
                "final_price_sum"=>$final_price_sum,
                "receivable_sum"=>$receivable_sum,
                "profit_sum"=>$profit_sum,
            );*/

            return json($result);
        }
        return $this->view->fetch();
    }

    /**
     * 指派
     * */
    public function appoint($ids)
    {
        $row = $this->model->get($ids);
        if ($this->request->isAjax()) {
            $params = $this->request->post("row/a");
            if ($params) {
                $params = $this->preExcludeFields($params);
                if (empty($params["engineer_id"])) $this->error("请选择工程师");
                if (empty($params["sale_id"])) $this->error("请选择销售");
                Db::startTrans();
                try {
                    $params["examinetime"] = time();
                    $params["hospital_id"] = $row["hospital_id"];
                    $params["status"] = '1';
                    //没填写维修记录
                    $user = [];
                    $sale = [];
                    if ($params["engineer_id"] != $row["engineer_id"]) {
                        $user = \app\common\model\User::where(["id" => $params["engineer_id"]])->find();
                    }
                    $user = \app\common\model\User::where(["id" => $params["engineer_id"]])->find();
                    if ($params["sale_id"] != $row["sale_id"]) {
                        $sale = \app\common\model\User::where(["id" => $params["sale_id"]])->find();
                    }
                    $row->allowField(true)->save($params);
                    /******************添加工作流  start******************************/
                    $workflow = array(
                        "warranty_id" => $ids,//工单id
                        "hospital_id" => $row["hospital_id"],//工单id
                        "user_id" => 0,//用户id
                        "type_data" => 7,//类型:0=成本,1=报价,2=方案,3=维修,4=勘察记录,5=保养,6=申请结单,7=平台,8=创建维修单
                        "type_id" => 0,//类型id
                        "title" => "工单受理成功",//标题
                        "content" => "您提交得报修工单已受理，将由专业人员为您服务，有任何疑问可联系平台客服。",//详情
                        "images" => !$this->request->param("images") ? '' : $this->request->param("images"),//图片/视频
                        "createtime" => time(),//创建时间
                    );
                    $equipment = Equipment::get(["id" => $row["equipment_id"]]);
                    $sms = new Sms();
                    $str = [
                        "wordID" => $row["workID"],
                        "name" => $equipment["name"],
                    ];
                    if ($user && $user["mobile"]) {
                        $ret = $sms->hairsendSms($user["mobile"], $str, 'SMS_245150564');
                    }
                    if ($sale && $sale["mobile"]) {
                        $ret = $sms->hairsendSms($sale["mobile"], $str, 'SMS_245150564');
                    }
                    Db::name("appoint")->insert(["engineer_id" => $params["engineer_id"], "warranty_id" => $ids, "sale_id" => $params["sale_id"], "createtime" => time(), "user_id" => 0]);
                    Workflow::create($workflow);
                    /******************添加工作流  end******************************/
                    Db::commit();
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                $this->success();
            }
        }
        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 成交价
     * */
    public function final($ids)
    {
        //$row = $this->model->get($ids);
        $row=$this->model->with(["hospital" => function ($query) {
            $query->withField('id,name,mobile');
        },
            "engineer"=>function ($query) {
                $query->withField('id,nickname,mobile');
            },
            "equipment"=>function ($query) {
                $query->withField('id,name');
            }])
            ->where(["warranty.id"=>$ids])
            ->find();
        if ($this->request->isAjax()) {
            $params = $this->request->post("row/a");
            if ($params) {
                $params = $this->preExcludeFields($params);
                if (empty($params["final_price"])) $this->error("请输入成本价");
                if($this->request->param("type")==1){
                    //首次填写成本价
                    if(!empty($row["engineer"]) && !empty($row["engineer"]["mobile"])){
                        $sms = new Sms();
                        $str = [
                            "wordID" => $row["workID"],
                            "hospital_name" => $row["hospital"]["name"],
                            "eqi_name" => $row["equipment"]["name"],
                        ];
                        $ret = $sms->hairsendSms($row["engineer"]["mobile"], $str, 'SMS_260480092');
                    }
                }
                /*2023-1-30 lina 完善*/
                $money=\app\admin\model\work\Xshk::where(["warranty_id"=>$ids])->sum("money");
                if(!empty($params["final_price"])){
                    if ($params["final_price"]<=$money && empty($row["collect_data"])){
                        $params["collect_data"]='1';
                        $params["collect_time"]=time();
                    }
                    if ($params["final_price"]>=$money && !empty($row["collect_data"])){
                        $params["collect_data"]='0';
                        $params["collect_time"]=0;
                    }
                }
                /*2023-1-30 lina 完善*/
                $row->allowField(true)->save($params);
                $this->success();
            }
        }
        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 详情
     * */
    public function see($ids)
    {
        $row = $this->model->with(["user" => function ($query) {
            $query->withField('id,nickname,avatar');
        }, "hospital" => function ($query) {
            $query->withField('id,name');
        }, "engineer" => function ($query) {
            $query->withField('id,nickname');
        }, "sale" => function ($query) {
            $query->withField('id,nickname');
        }, "equipment" => function ($query) {
            $query->withField('id,name,marking,serial,enclosure,software_version');
        }])
            ->where(["warranty.id" => $ids])
            ->find();
        if (!empty($row["images"])) $row["images"] = explode(",", $row["images"]);
        if (!empty($row["billing_images"])) $row["billing_images"] = explode(",", $row["billing_images"]);
        /*if (!empty($row["final_price"]) && $row["final_price"] > 0) {
            $cost = Cost::where(["warranty_id" => $row["id"], "status" => '1'])->sum("money");
            $row->profit = $row["final_price"] - $cost;
        } else {
            $row->profit = "——";
        }*/
        $cost = Cost::where(["warranty_id" => $row["id"], "status" => '1'])->sum("money");
        $row->profit = $row["final_price"] - $cost;
        if($row["type_data"]==1){
            //维保工单
            $service=\app\common\model\work\Service::field("servicetime")->where(["warranty_id"=>$row["id"]])->order("servicetime","desc")->find();
            if($service) $row["servicetime"]=$service["servicetime"];
        }
        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 开票
     * */
    public function billing($ids)
    {
        $row = $this->model->get($ids);
        if ($this->request->isAjax()) {
            $params = $this->request->post("row/a");
            if ($params) {
                $params = $this->preExcludeFields($params);
                if (empty($row["final_price"])) $this->error("请输入成交价");
                $row->allowField(true)->save($params);
                $this->success();
            }
        }
        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 新增
     * */
    public function selectpage2()
    {
        //当前页
        $page = $this->request->request("pageNumber");
        //分页大小
        $pagesize = $this->request->request("pageSize");
        /*if($this->request->request("searchKey")){
            $where="  id in (".$this->request->param("ids").")" ;
        }else{
            if($this->request->param("ids")){
                $where=" id NOT IN (SELECT
SUBSTRING_INDEX( SUBSTRING_INDEX( a.user_ids, ',', b.help_topic_id + 1 ), ',',- 1 ) id
FROM yl_hospital a
JOIN help_topic b ON b.help_topic_id < ( LENGTH( a.user_ids ) - LENGTH( REPLACE ( a.user_ids, ',',''))+1)
) or id in (".$this->request->param("ids").")" ;
            }else{
                $where=" id NOT IN (SELECT
SUBSTRING_INDEX( SUBSTRING_INDEX( a.user_ids, ',', b.help_topic_id + 1 ), ',',- 1 ) id
FROM yl_hospital a
JOIN help_topic b ON b.help_topic_id < ( LENGTH( a.user_ids ) - LENGTH( REPLACE ( a.user_ids, ',',''))+1)
) " ;
            }
        }*/
        $where = "1=1";
        $datalist = $this->model->alias("warranty")
            ->field("warranty.id,workID,a.name,a.marking")
            ->join("equipment a", "a.id=warranty.equipment_id", 'left')
            ->whereLike("name", "%" . $this->request->param("name") . "%")
            ->where($where)
            ->page($page, $pagesize)
            ->select();
        $total = $this->model->alias("warranty")
            ->join("equipment a", "a.id=warranty.equipment_id", 'left')
            ->whereLike("name", "%" . $this->request->param("name") . "%")
            ->where($where)
            ->count();
        return json(['list' => $datalist, 'total' => $total]);
    }
    /******************** 新增 2022-7-12*********************************/
    /**
     *更改状态为已提成
     * */
    public function comm($ids)
    {
        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }
        $result = $row->allowField(true)->save(["comm_data" => '1']);
        if ($result !== false) {
            $this->success();
        } else {
            $this->error(__('No rows were updated'));
        }
    }

    /**/
    public function designate()
    {
        /*$electronics=new \app\admin\controller\electronics\Index();
        $res=$electronics->logistics([
            "express_code"=>9850047411651,
            "express_id"=>10021,
        ]);*/
        $warranty_id = $this->request->param("warranty_id");
        if ($this->request->isAjax()) {
            try {
                $sort = "createtime";
                $order = "desc";
                $limit = $this->request->get("limit/d", 10);
                $offset = $this->request->get("offset/d", 0);
                //新增自动计算页码
                $page = $limit ? intval($offset / $limit) + 1 : 1;
                if ($this->request->has("page")) {
                    $page = $this->request->get("page/d", 1);
                }
                $this->request->get([config('paginate.var_page') => $page]);
                $list = Appoint::where(["warranty_id" => $warranty_id])
                    ->with(["engineer" => function ($query) {
                        $query->withField('id,nickname');
                    }, "sale" => function ($query) {
                        $query->withField('id,nickname');
                    }])
                    ->order($sort, $order)
                    ->paginate($limit)->each(function ($v) {
                        $v["createtime"] = date('Y-m-d H:i:s', $v["createtime"]);
                    });
                $result = array("total" => $list->total(), "rows" => $list->items());
                return json($result);
            } catch (Exception $e) {
                $this->error($e->getMessage());
            }
        } else {
            return $this->view->fetch('', ['id' => $warranty_id]);
        }
    }
    /***********  新增 ****************/
    /**
     * 亏损
     * */
    public function loss($ids)
    {
        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }
        if ($row["is_loss"] == 0) {
            $result = $row->allowField(true)->save(["is_loss" => '1', "loss_time" => time()]);
            if ($result !== false) {
                $this->success();
            } else {
                $this->error(__('No rows were updated'));
            }
        } else {
            $this->success();
        }
    }
    /*
     * 导出
     * */
    public function export()
    {
        if ($this->request->isPost()) {
            //当前是否为关联查询
            $this->relationSearch = true;
            set_time_limit(0);
            $search = $this->request->post('search');
            $ids = $this->request->post('ids');
            $filter = $this->request->post('filter');
            $filter = (array)json_decode($filter, true);
            $op = $this->request->post('op');
            if (array_key_exists("status", $filter)) {
                if (in_array($filter["status"], [4, 5, 6, 7])) {
                    $op = json_decode($this->request->request("op"), true);
                    $filter["warranty_time"] = time();
                    $op["warranty_time"] = ">";
                    $filter["eng_com"] = 0;
                    $op["eng_com"] = "=";
                    $filter["is_loss"] = 0;
                    $op["is_loss"] = "=";
                    if (in_array($filter["status"], [5, 6])) $op["warranty_time"] = "<";
                    if ($filter["status"] == 6) {
                        unset($filter["eng_com"]);
                        unset($op["eng_com"]);
                        $filter["ok_status"] = 0;
                        $op["ok_status"] = "=";
                    }
                    if ($filter["status"] == 3) {
                        $filter["ok_status"] = 1;
                        $op["ok_status"] = "=";
                    }
                    if ($filter["status"] != 7) {
                        $filter["status"] = 3;
                        $filter["is_loss"] = 0;
                        $op["is_loss"] = "=";
                    }

                    if ($filter["status"] == 7) {
                        unset($filter["eng_com"]);
                        unset($op["eng_com"]);
                        unset($filter["warranty_time"]);
                        unset($op["warranty_time"]);
                        unset($filter["status"]);
                        unset($op["status"]);
                        $filter["is_loss"] = 1;
                    }
                } else {
                    if ($filter["status"] != 7) {
                        $filter["is_loss"] = 0;
                        $op["is_loss"] = "=";
                    }
                }
                //$this->request->get(["filter" => json_encode($filter), 'op' => json_encode($op)]);
            }

            //$excel = new PHPExcel();
            $spreadsheet = new Spreadsheet();

            $spreadsheet->getProperties()
                ->setCreator("FastAdmin")
                ->setLastModifiedBy("FastAdmin")
                ->setTitle("标题")
                ->setSubject("Subject");
            $spreadsheet->getDefaultStyle()->getFont()->setName('Microsoft Yahei');
            $spreadsheet->getDefaultStyle()->getFont()->setSize(12);

            $worksheet = $spreadsheet->setActiveSheetIndex(0);
            $whereIds = $ids == 'all' ? '1=1' : ['warranty.id' => ['in', explode(',', $ids)]];
            $this->request->get(['search' => $search, 'ids' => $ids, 'filter' => json_encode($filter), 'op' => $op]);
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $line = 1;
            //设置过滤方法
            $this->request->filter(['strip_tags']);

            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            /*$total = $this->model
                ->where($whereIds)
                ->order($sort, $order)
                ->count();*/
            $hospital = Hospital::field("id")->where(["status" => 1])
                ->where('FIND_IN_SET("' . $this->auth->id . '", admin_ids)')->find();
            $where1 = '1=1';
            if ($hospital) $where1 = " warranty.hospital_id = " . $hospital["id"];
            $total = $this->model
                ->with(['user', 'hospital', 'equipment',
                    'engineer' => function ($query) {
                        $query->withField('id,nickname');
                    },
                    'sale' => function ($query) {
                        $query->withField('id,nickname');
                    }])
                ->where($whereIds)
                ->where($where)
                ->where($where1)
                ->where(["warranty.type_data" => '0'])
                ->count();
            $list = $this->model
                ->with(['user', 'hospital', 'equipment',
                    'engineer' => function ($query) {
                        $query->withField('id,nickname');
                    },
                    'sale' => function ($query) {
                        $query->withField('id,nickname');
                    }])
                ->where($whereIds)
                ->where($where)
                ->where($where1)
                ->where(["warranty.type_data" => '0'])
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();
            /*$list = $this->model
                ->where($whereIds)
                ->order($sort, $order)
                ->limit($offset, $limit)
                ->select();*/

            $list = collection($list)->toArray();
            $receivable=0;
            foreach ($list as &$row){
                //$xshk = \app\admin\model\work\Xshk::where(["warranty_id" => $row["id"]])->sum("money");
                $xshk = \app\admin\model\work\Xshk::with("warranty")->whereIn("warranty_id", $row["id"])->where($where1)->where(["type_data" => "0"])->sum("money");//回款记录
                $row["cost_money"] = Cost::where(["warranty_id" => $row["id"], "status" => '1'])->sum("money");
                $row["xshk"] = $xshk;
               /* if (!empty($row["final_price"]) && $row["final_price"] > 0) {
                    $row["profit"] = $row["final_price"] - $row["cost_money"];
                    $receivable = $row["final_price"] - $xshk;
                }*/
                $row["profit"] = $row["final_price"] - $row["cost_money"];
                $receivable = $row["final_price"] - $xshk;
                $row["receivable"] = $receivable;
            }
            //dump($xshk1);die();
            $title = ['工单ID', '医院名称', '设备名称', '型号', '序列号', '工程师', '销售', '成交价','状态','是否开票','创建时间','成本价','质保到期时间','利润','回款金额','应收账款','收款状态'];
            $filter = $this->request->post('filter');
            $filter = (array)json_decode($filter, true);
            if(array_key_exists("status", $filter) && $filter["status"]==4){
                //保内
                $columns='workID,hospital.name,equipment.name,equipment.marking,equipment.serial,sale.nickname,engineer.nickname,final_price';
                $title = ['工单ID', '医院名称', '设备名称', '型号', '序列号', '工程师', '销售', '成交价'];
            }
            $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            // 方法一，使用 setCellValueByColumnAndRow
            //表头
            //设置单元格内容
            foreach ($title as $key => $value) {
                // 单元格内容写入
                $sheet->setCellValueByColumnAndRow($key + 1, 1, $value);
            }
            $newdata=[];
            foreach ($list as $k=>$v){
                $newdata[$k]=[
                    "workID"=>$v["workID"],
                    "hospital.name"=>$v["hospital"]["name"]??'',
                    "equipment.name"=>$v["equipment"]["name"]??'',
                    "equipment.marking"=>$v["equipment"]["marking"]??'',
                    "equipment.serial"=>$v["equipment"]["serial"]??'',
                    "sale.nickname"=>$v["sale"]["nickname"]??'',
                    "engineer.nickname"=>$v["engineer"]["nickname"]??'',
                    "final_price"=>$v["final_price"]?? 0,

                    "status"=>$v["status_text"]?? '',
                    "billing_data"=>$v["billing_data_text"]?? '',
                    "createtime"=>date('Y-m-d H:i:s',$v["createtime"])?? '',
                    "cost_money"=>$v["cost_money"]?? 0,
                    "warranty_time"=>$v["warranty_time_text"]?? '',
                    "profit"=>$v["profit"]?? 0,
                    "xshk"=>$v["xshk"]?? 0,
                    "receivable"=>$v["receivable"]??0,
                    "collect_data"=>$v["collect_data_text"]?? '',
                ];
                if(array_key_exists("status", $filter) && $filter["status"]==4){
                    unset($newdata[$k]["status"]);
                    unset($newdata[$k]["billing_data"]);
                    unset($newdata[$k]["createtime"]);
                    unset($newdata[$k]["cost_money"]);
                    unset($newdata[$k]["warranty_time"]);
                    unset($newdata[$k]["profit"]);
                    unset($newdata[$k]["xshk"]);
                    unset($newdata[$k]["receivable"]);
                    unset($newdata[$k]["collect_data"]);
                }
            }
            $row = 2; // 从第二行开始
            foreach ($newdata as $item) {
                $column = 1;
                foreach ($item as $k=>$value) {
                    $sheet->setCellValueByColumnAndRow($column, $row, $value);
                    $column++;
                }
                $row++;
            }
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename=' . date('Y-m-d') .'_test'. '.xlsx');
            header('Cache-Control: max-age=0');
            // If you're serving to IE 9, then the following may be needed
            header('Cache-Control: max-age=1');

            // If you're serving to IE over SSL, then the following may be needed
            header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
            header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
            header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
            header('Pragma: public'); // HTTP/1.0

            $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
            $writer->save('php://output');

            die();

            $first = array_keys($list[0]);
            foreach ($first as $index => $item) {
                $worksheet->setCellValueByColumnAndRow($index, 1, __($item));
            }
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load(ROOT_PATH . 'public/assets/muban.xlsx');  //读取模板
            $worksheet = $spreadsheet->getActiveSheet();     //指向激活的工作表
            $worksheet->setTitle('模板测试标题');
            for($i=0;$i<$total;++$i){
                //向模板表中写入数据
                $i++;
                $columns='workID,hospital.name,equipment.name,equipment.marking,equipment.serial,sale.nickname,engineer.nickname,final_price';
                $worksheet->getCell('A'.$i)->setValue($result['rows'][$i]['workID']);    //星期
                $worksheet->getCell('B'.$i)->setValue($result['rows'][$i]['hospital']["name"]);    //星期
                $worksheet->getCell('C'.$i)->setValue($result['rows'][$i]['equipment']["name"]);  //性别
                $worksheet->getCell('D'.$i)->setValue($result['rows'][$i]['equipment']["marking"]);  //爱好
                $worksheet->getCell('E'.$i)->setValue($result['rows'][$i]['equipment']["serial"]);  //标题
                $worksheet->getCell('F'.$i)->setValue($result['rows'][$i]['sale']["nickname"]);  //内容
                $worksheet->getCell('G'.$i)->setValue($result['rows'][$i]['engineer']["nickname"]);  //内容
                $worksheet->getCell('H'.$i)->setValue($result['rows'][$i]['final_price']);  //内容
                //$worksheet->getCell('I'.$i)->setValue($result['rows'][$i]['final_price']);  //内容
                $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
                //下载文档
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="'. date('Y-m-d') .'_test'.'.xlsx"');
                header('Cache-Control: max-age=0');
                $writer->save('php://output');

            }
            return;
        }
    }
}
